﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Entity;
using Microsoft.Practices.EnterpriseLibrary.Data;
using EntLib.Domain.Common;

namespace SQLServerDAL
{
    public abstract class DataAccess<T> where T : ISetProperty, new()
    {
        protected string _sql;

        protected Database db = null;

        protected DbTransaction _transaction { get; set; }

        private DbConnection _connection { get; set; }

        /// <summary>
        /// 启动一个事务
        /// </summary>
        protected void StartTran()
        {
            _connection = db.CreateConnection();
            _connection.Open();
            _transaction = _connection.BeginTransaction();
        }

        /// <summary>
        /// 取消事务，回滚
        /// </summary>
        protected void CancelTran()
        {
            _transaction.Rollback();
            EndTran();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        protected void CommitTran()
        {
            _transaction.Commit();
            EndTran();
        }

        /// <summary>
        /// 结束事物
        /// </summary>
        private void EndTran()
        {
            if (_transaction != null)
            {
                _transaction.Dispose();
            }

            if (_connection != null)
            {
                _connection.Close();
                _connection.Dispose();
            }
        }

        /// <summary>
        /// 默认构造函数，使用默认配置连接数据库
        /// </summary>
        protected DataAccess()
        {
            db = DatabaseFactory.CreateDatabase("FanHuan");
        }

        /// <summary>
        /// 构造函数，使用指定的连接字符串连接数据库
        /// </summary>
        /// <param name="connectString">连接名</param>
        protected DataAccess(string connectString)
        {
            db = DatabaseFactory.CreateDatabase(connectString);
        }

        /// <summary>
        /// 获取一个对象实例
        /// </summary>
        /// <param name="cmd">命令</param>
        protected T ExecuteReader(DbCommand cmd)
        {
            T model = new T();            
            using (IDataReader idr = db.ExecuteReader(cmd))
            {
                if (idr.Read())
                {
                    var fields = CommHelper.GetFieldsFromIDataReader(idr);
                    model.SetProperty(idr, fields);
                    return model;
                }
                return default(T);
            }
        }

        /// <summary>
        /// 获取多个对象实例
        /// </summary>
        /// <param name="cmd">命令</param>
        protected List<T> ExecuteReaderList(DbCommand cmd)
        {
            List<T> ModelList = new List<T>();
            using (IDataReader idr = db.ExecuteReader(cmd))
            {
                var fields = CommHelper.GetFieldsFromIDataReader(idr);
                while (idr.Read())
                {
                    T item = new T();
                    item.SetProperty(idr, fields);
                    ModelList.Add(item);
                }
                return ModelList;
            }
        }

        /// <summary>
        /// 获取分页的内容
        /// </summary>
        /// <param name="db">数据库</param>
        /// <param name="tableName">表名</param>
        /// <param name="output">输出字段</param>
        /// <param name="orderBy">排序</param>
        /// <param name="filter">过滤（where语句的内容）</param>
        /// <param name="pageSize">页大小（每页记录数）</param>
        /// <param name="pageIndex">页码（当前要获取的数据所在页）</param>
        /// <param name="total">总数</param>
        protected List<T> GetPageDetail(Database db, string tableName, string output, string orderBy, string filter, int pageSize, int pageIndex, out int total)
        {
            List<T> result = new List<T>();
            using (DbCommand cmd = db.GetStoredProcCommand("UtilPage"))
            {
                db.AddInParameter(cmd, "@datasrc", DbType.String, tableName);
                db.AddInParameter(cmd, "@fieldlist", DbType.String, output);
                db.AddInParameter(cmd, "@orderby", DbType.String, orderBy);
                db.AddInParameter(cmd, "@filter", DbType.String, filter);
                db.AddInParameter(cmd, "@PageNum", DbType.Int32, pageIndex);
                db.AddInParameter(cmd, "@PageSize", DbType.Int32, pageSize);

                using (IDataReader idr = db.ExecuteReader(cmd))
                {
                    // 获取总数
                    total = 0;
                    if (idr.Read())
                    {
                        total = (int)idr[0];
                    }
                    // 获取数据
                    if (idr.NextResult())
                    {
                        var fields = CommHelper.GetFieldsFromIDataReader(idr);
                        while (idr.Read())
                        {
                            T item = new T();
                            item.SetProperty(idr,fields);
                            result.Add(item);
                        }
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 支持多表联合查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <param name="tableSource"></param>
        /// <param name="output"></param>
        /// <param name="orderBy"></param>
        /// <param name="where"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        protected List<T> GetPageDetailByJoin(Database db, string tableSource, string output, string orderBy, string where, int pageSize, int pageIndex, out int total)
        {
            List<T> result = new List<T>();
            using (DbCommand cmd = db.GetStoredProcCommand("UtilPageByJoin"))
            {
                db.AddInParameter(cmd, "@datasrc", DbType.String, tableSource);
                db.AddInParameter(cmd, "@fieldlist", DbType.String, output);
                db.AddInParameter(cmd, "@orderby", DbType.String, orderBy);
                db.AddInParameter(cmd, "@filter", DbType.String, where);
                db.AddInParameter(cmd, "@PageNum", DbType.Int32, pageIndex);
                db.AddInParameter(cmd, "@PageSize", DbType.Int32, pageSize);

                using (IDataReader idr = db.ExecuteReader(cmd))
                {
                    total = 0;
                    if (idr.Read())
                    {
                        total = (int)idr[0];
                    }
                    if (idr.NextResult())
                    {
                        var fields = CommHelper.GetFieldsFromIDataReader(idr);
                        while (idr.Read())
                        {
                            T item = new T();
                            item.SetProperty(idr,fields);
                            result.Add(item);
                        }
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 分页 where条件在tablename一起
        /// </summary>
        /// <param name="db"></param>
        /// <param name="tableName"></param>
        /// <param name="output"></param>
        /// <param name="orderBy"></param>
        /// <param name="where"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        protected List<T> GetPageDetailNoWhere(Database db, string tableName, string output, string orderBy, int pageSize, int pageIndex, out int total)
        {
            List<T> result = new List<T>();
            using (DbCommand cmd = db.GetStoredProcCommand("UtilPageNoWhere"))
            {
                db.AddInParameter(cmd, "@datasrc", DbType.String, tableName);
                db.AddInParameter(cmd, "@fieldlist", DbType.String, output);
                db.AddInParameter(cmd, "@orderby", DbType.String, orderBy);
                // db.AddInParameter(cmd, "@filter", DbType.String, where);
                db.AddInParameter(cmd, "@PageNum", DbType.Int32, pageIndex);
                db.AddInParameter(cmd, "@PageSize", DbType.Int32, pageSize);

                using (IDataReader idr = db.ExecuteReader(cmd))
                {
                    total = 0;
                    if (idr.Read())
                    {
                        total = (int)idr[0];
                    }
                    if (idr.NextResult())
                    {
                        var fields = CommHelper.GetFieldsFromIDataReader(idr);
                        while (idr.Read())
                        {
                            T item = new T();
                            item.SetProperty(idr,fields);
                            result.Add(item);
                        }
                    }
                }
            }
            return result;
        }

    }
}
